# This is a sample Python script.
import sqlite3
import pandas as pd
import format_convert
# parameter a = ' : '
a = ' : '
# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.

# read the excel in "D:\Projects\Android\Now_word-main-2\app\src\main\assets\jp_zhongji.xlsx"
def read_excel():
    df = pd.read_excel(r'D:\Projects\Android\Now_word-main-2\app\src\main\assets\jp_zhongji.xlsx')
    # get the column of 'headWord' , 'tranCN' , 'tranEN' , 'proun' and add them to a str list
    headWord = df['headWord'].tolist()
    tranCN = df['tranCN'].tolist()
    tranEN = df['tranEN'].tolist()
    proun = df['proun'].tolist()
    # make tranCN the appendix of proun
    # print lists above
    print(headWord)
    print(tranCN)
    print(tranEN)
    print(proun)
    for i in range(len(headWord)):
        tranCN[i] = format_convert.str_concat(proun[i], tranCN[i])
    # open sqlite database now_word.db and insert the data into it
    conn = sqlite3.connect('now_word.db')
    c = conn.cursor()
    count = 1
    for i in range(len(headWord)):
        c.execute("INSERT INTO tb_word(wordRank, headWord, sentences, tranCN, tranEN, wordType) VALUES (?,?,'none',?,?,'JLPT N3')", (count, headWord[i], tranCN[i], tranEN[i]))
        count += 1
    conn.commit()
    conn.close()

    return df



def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')
    read_excel()
